package com.sl.au.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Slice;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import com.sl.au.entity.Division;
import com.sl.au.entity.GridAreaInfo;

public interface GridAreaInfoRepository extends JpaRepository<GridAreaInfo, String> {
	@Query("select g from GridAreaInfo g where g.gridIdentify like %?1% "
			+ "or g.gridState.title like %?1% or g.typeOpt.title like %?1% or g.division.divisionName like %?1% or g.gridName like %?1%")
	Page<GridAreaInfo> getPageByKey(String key, Pageable pageable);

	@Query("select g from GridAreaInfo g where (g.gridIdentify like %?1%  "
			+ "or g.gridState.title like %?1% or g.typeOpt.title like %?1% or g.division.divisionName like %?1% or g.gridName like %?1%) and g.division is not null")
	Page<GridAreaInfo> getBindDivisionPageByKey(String key, Pageable pageable);

	@Query("select g from GridAreaInfo g where g.division.id=?1 and (g.gridIdentify like %?2% "
			+ "or g.gridState.title like %?2% or g.typeOpt.title like %?2% or g.division.divisionName like %?2% or g.gridName like %?2%) and g.typeOpt.title = ?3")
	Page<GridAreaInfo> getBindDivisionPageWithAreaIdByKey(String areaId, String key,String title, Pageable pageable);
	
	@Query("select g from GridAreaInfo g where (g.gridIdentify like %?2% "
			+ "or g.gridState.title like %?2% or g.typeOpt.title like %?2% or g.division.divisionName like %?2% or g.gridName like %?2%) and g.typeOpt.title = ?1")
	Page<GridAreaInfo> getPageWithTypeNameByKey(String title,String key, Pageable pageable);

	@Query("select g from GridAreaInfo g where g.division.id=?1 and g.typeOpt.title = ?2")
	Page<GridAreaInfo> getBindDivisionPageWithAreaId(String areaId,String title, Pageable pageable);
	
	@Query("select g from GridAreaInfo g where g.typeOpt.title = ?1")
	Page<GridAreaInfo> getPageWithTypeName(String title, Pageable pageable);
	
	@Query("select g from GridAreaInfo g where g.typeOpt.title = ?1")
	List<GridAreaInfo> getPageWithTypeName(String title);
	
	@Query("select g from GridAreaInfo g where g.typeOpt.title = '特征点' and g.division.id=?1 and (g.gridIdentify like %?2% "
			+ "or g.gridState.title like %?2% or g.typeOpt.title like %?2% or g.division.divisionName like %?2% or g.gridName like %?2%)")
	Page<GridAreaInfo> getPointBindDivisionPageWithAreaIdByKey(String areaId, String key, Pageable pageable);
	
	@Query("select g from GridAreaInfo g where g.division is null and g.typeOpt.title = ?1")
	List<GridAreaInfo> getUnbindDivision(String title);
	
	@Query("select g from GridAreaInfo g where g.typeOpt.title = '特征点' and g.division.id=?1")
	Page<GridAreaInfo> getPointBindDivisionPageWithAreaId(String areaId, Pageable pageable);

	@Query("select g from GridAreaInfo g where g.typeOpt.title = '特征点' and g.division is null and (g.gridIdentify like %?1% "
			+ "or g.gridState.title like %?1% or g.typeOpt.title like %?1% or g.gridName like %?1%)")
	Page<GridAreaInfo> getPointUnbindDivisionPageByKey(String key, Pageable pageable);

	@Query("select g from GridAreaInfo g where g.typeOpt.title = '特征点' and g.division is null")
	Page<GridAreaInfo> getPointUnbindDivisionPage(Pageable pageable);
	
	@Query("select g from GridAreaInfo g where  g.division is not null")
	Page<GridAreaInfo> getBindDivisionPage(Pageable pageable);
	
	@Query("select g from GridAreaInfo g where  g.division.id=?1 and g.typeOpt.title = ?2")
	List<GridAreaInfo> getBindByDivision(String areaId,String title);

	@Query("select g from GridAreaInfo g where  g.division.id=?1 and g.typeOpt.title = '特征点'")
	List<GridAreaInfo> getBindPointByDivision(String areaId);

	@Query("select g from GridAreaInfo g where g.division is null and (g.gridIdentify like %?1% "
			+ "or g.gridState.title like %?1% or g.typeOpt.title like %?1% or g.gridName like %?1%) and g.typeOpt.title = ?2")
	Page<GridAreaInfo> getUnbindDivisionPageByKey(String key,String title, Pageable pageable);

	@Query("select g from GridAreaInfo g where  g.division is null and g.typeOpt.title = '特征点'")
	List<GridAreaInfo> getUnBindPoint();
	
	@Query("select g from GridAreaInfo g where g.division is null and g.typeOpt.title = ?1")
	Page<GridAreaInfo> getUnbindDivisionPage(String title,Pageable pageable);

	Page<GridAreaInfo> findByDivision(Division division, Pageable pageable);
	
	@Query("select g from GridAreaInfo g where g.division.id = ?1")
	Page<GridAreaInfo> findByDivision(String divisionId, Pageable pageable);

	@Query("select g from GridAreaInfo g where g.division.id = ?1 and g.gridName like %?2%")
	Page<GridAreaInfo> findByDivisionAndKey(String divisionId, String Key, Pageable pageable);

	@Query("select g from GridAreaInfo g where g.division.id = ?1 and g.typeOpt.title = ?2")
	Page<GridAreaInfo> findByDivisionAndType(String divisionId, String title,Pageable pageable);
	
	@Query("select g from GridAreaInfo g where g.division.id = ?1 and g.typeOpt.title = ?2 and g.gridName like %?3%")
	Page<GridAreaInfo> findByDivisionAndTypeAndKey(String divisionId, String title, String key, Pageable pageable);

	@Query("select count(g) from GridAreaInfo g where g.division.id = ?1 and g.typeOpt.title = ?2")
	int getCountByDivisionAndType(String divisionId, String title);
	
	@Query("select count(g) from GridAreaInfo g where g.division.id = ?1 and g.typeOpt.title = ?2 and g.gridName like %?3%")
	int getCountByDivisionAndTypeAndKey(String divisionId, String title, String key);

	@Query("select g from GridAreaInfo g join g.users u where u.id=?1")
	Page<GridAreaInfo> getGridBindUserPageByKey(String key, Pageable pageable);

	@Query("select g from GridAreaInfo g join g.users u where u.id=?1 and (g.gridIdentify like %?2% "
			+ "or g.gridState.title like %?2% or g.typeOpt.title like %?2% or g.division.divisionName like %?2% or g.gridName like %?2%)")
	Page<GridAreaInfo> getGridBindUserPageByKeyAndCon(String key, String con, Pageable pageable);

	@Query("select g from GridAreaInfo g where g.division.id=?1 and (g.gridIdentify like %?2% "
			+ "or g.gridState.title like %?2% or g.typeOpt.title like %?2% or g.gridName like %?2%)")
	Page<GridAreaInfo> findBindDivisionCountByKeyAndCon(String key, String con, Pageable pageable);

	@Query("select g from GridAreaInfo g where g.division.id=?1")
	Page<GridAreaInfo> findBindDivisionPageOnlyKey(String key, Pageable pageable);

	@Query("select g from GridAreaInfo g where g.division is not null and (g.gridIdentify like %?1% or g.gridArea like %?1% "
			+ "or g.gridState.title like %?1% or g.typeOpt.title like %?1% or g.gridName like %?1%)")
	Page<GridAreaInfo> findBindDivisionCountOnlyCon(String con, Pageable pageable);

	@Query("select g from GridAreaInfo g where  g.division is not null")
	Page<GridAreaInfo> findBindDivisionPageNone(Pageable pageable);

	@Query("select g from GridAreaInfo g where g.division is null and (g.gridIdentify like %?1% "
			+ "or g.gridState.title like %?1% or g.typeOpt.title like %?1% or g.gridName like %?1%)")
	Page<GridAreaInfo> findUnbindDOnlyCon(String con, Pageable pageable);

	@Query("select g from GridAreaInfo g where g.division is null")
	Page<GridAreaInfo> findUnbindDNone(Pageable pageable);

	@Query("select g from GridAreaInfo g where g.division.id!= ?1 or g.division is null and (g.gridIdentify like %?2% "
			+ "or g.gridState.title like %?2% or g.typeOpt.title like %?2% or g.gridName like %?2%)")
	Page<GridAreaInfo> findUnbindDByKeyAndCon(String key, String con, Pageable pageable);

	@Query("select g from GridAreaInfo g where g.division.id!=?1 or g.division is null")
	Page<GridAreaInfo> findUnbindOnlyKey(String key, Pageable pageable);
	
	/********* 网格清除区域信息 **********/
	@Modifying
	@Query("update GridAreaInfo a set a.division = null , a.gridIdentify = null  where a.division.id in (?1)")
	int deleteDivision(String[] ids);

	GridAreaInfo findByGridName(String name);

}
